<?php

namespace app\admin_platform\controller;

use PHPExcel_IOFactory;
use PHPExcel_Reader_CSV;
use think\Controller;
use think\Db;
use think\Request;

class Transaction extends Base
{
    //
    public function showimport()
    {
        return view('transaction/showimport');
    }

    public function doimport()
    {
        Db::startTrans();
        try {
            $file = request()->file('file');
            $info = $file->validate(['ext' => 'csv,xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'uploads');
            if ($info) {
                if (empty($info)) {
                    //                return $this->ERROR('导入失败!');
                    return "<script>alert('导入失败');history.go(-1)</script>";
                }
                $exclePath = $info->getSaveName();  //获取文件名
                //上传文件的地址
                $filename = ROOT_PATH . 'public' . DS . 'uploads' . DS . $exclePath;
                $extension = strtolower(pathinfo($filename, PATHINFO_EXTENSION));
                if ($extension == 'xlsx') {
                    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
                    //加载文件内容,编码utf-8
                    $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');
                } else if ($extension == 'xls') {
                    $objReader = PHPExcel_IOFactory::createReader('Excel5');
                    $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');
                } else if ($extension == 'csv') {
                    $PHPReader = new PHPExcel_Reader_CSV();
                    //默认输入字符集
                    $PHPReader->setInputEncoding('GBK');
                    //默认的分隔符
                    $PHPReader->setDelimiter(',');
                    //载入文件
                    $objPHPExcel = $PHPReader->load($filename);
                } else {
                    //                return error('请上传符合格式的文件!');
                    return "<script>alert('请上传符合格式的文件');history.go(-1)</script>";
                }
                $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式

                array_shift($excel_array);  //删除第一个数组(标题);
                $data = [];
                foreach ($excel_array as $k => $v) {
                    //data数组根据你表字段自行修改，这里Excel文件里的字段要跟表一致
                    $data[$k]['Subaccount'] = $v[0]; //子账号
                    $agent=Db::name('users')->field('id,leaguer,agents')->where('assetaccount',$v[0])->find();
                    $data[$k]['uid']=$agent['id'];
                    $data[$k]['agents']=$agent['agents'];
                    $data[$k]['vip']=$agent['leaguer'];
                    $data[$k]['Grouping'] = $v[1]; //所属分组
                    $data[$k]['TradingDday'] = $v[2]; //交易日
                    $data[$k]['Entrustment'] = $v[3]; //委托号
                    $data[$k]['Deal'] = $v[4];       //成交号
                    $data[$k]['Request'] = $v[5];    //请求号
                    $data[$k]['Exchange'] = $v[6];   //交易所
                    $data[$k]['Contract'] = $v[7];   //合约
                    $data[$k]['Business'] = $v[8];   //买卖
                    $data[$k]['Kaiping'] = $v[9];    //开平
                    $data[$k]['Insure'] = $v[10];    //投保
                    $data[$k]['Price'] = $v[11];  //成交价
                    $data[$k]['Count'] = $v[12];     //成交量
                    $data[$k]['Amount'] = $v[13];   //成交金额
                    $data[$k]['Date'] = $v[14];     //实际日期
                    $data[$k]['Time'] = $v[15];     //成交时间
                    $data[$k]['PrimaryAccount'] = $v[16];  //主账号
                    $data[$k]['System'] = $v[17];     //系统号
                    $data[$k]['Poundage'] = $v[18];   //手续费
                    $data[$k]['Float'] = $v[19];      //平仓浮动盈亏
                    $data[$k]['Profit'] = $v[20];     //平仓盈亏
                    $data[$k]['Brokerage'] = $v[21];  //基础手续费
                }
                foreach ($data as $k => $v) {
                    $temp = $v['TradingDday'];
                    if ($temp) {
                        break;
                    }
                }
                if (empty($temp)) {
                    return "<script>alert('请确认数据无误后导入');history.go(-1)</script>";
                }
                $res = Db::name('transaction')->where('TradingDday', $temp)->find();
                if ($res) {
                    return "<script>alert('" . $temp . "号数据已导入，请勿重复导入');history.go(-1)</script>";
                }
                $res = Db::name('transaction')->insertAll($data);
                if ($res) {
                    Db::commit();
                    return "<script>alert('导入成功');history.go(-1)</script>";
                }

            } else {
                return "<script>alert('导入失败');history.go(-1)</script>";
            }
        }catch (\Exception $e) {
            Db::rollback();
            return "<script>alert('导入失败');history.go(-1)</script>";
        }
    }

    public function showlist(Request $request)
    {
        $get=$request->get();
        $get['leaguer']=isset($get['leaguer'])?$get['leaguer']:'0';
        $get['agents']=isset($get['agents'])?$get['agents']:'0';
        $get['acc']=isset($get['acc'])?$get['acc']:'0';
        $get['kp']=isset($get['kp'])?$get['kp']:'开,平,平今';
        $get['mm']=isset($get['mm'])?$get['mm']:'买,卖';
        $get['hy']=isset($get['hy'])?$get['hy']:'';
        $get['zzh']=isset($get['zzh'])?$get['zzh']:'';
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('-1 day'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d');
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);

        $vipall=Db::name('users')->field('id,name')->where('register',1)->select();
        foreach ($vipall as $k=>$v){
            if($v['id']==$get['leaguer']){
                $vipall[$k]['status']=1;
            }else{
                $vipall[$k]['status']=0;
            }
        }
        $acc=Db::name('primary_account')->field('account')->select();
        foreach ($acc as $k=>$v){
            if($v['account']==$get['acc']){
                $acc[$k]['status']=1;
            }else{
                $acc[$k]['status']=0;
            }
            $acctemp[$k]=$v['account'];
        }

        if($get['acc']==0){
            $acctemp1=$acctemp;
            $acctempop='in';
        }else{
            $acctemp1=$get['acc'];
            $acctempop='eq';
        }
        $kp=explode(',',$get['kp']);
        $mm=explode(',',$get['mm']);
        if($get['agents']==0){
            // 所有代理商
            if($get['leaguer']==0){
                $agentsall=[];
                $count['Count']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->order('id desc')
                    ->sum('Count');
                $count['Poundage']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->order('id desc')
                    ->sum('Poundage');
                $count['Float']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->order('id desc')
                    ->sum('`Float`');
                $count['Profit']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->order('id desc')
                    ->sum('Profit');
                $count['Brokerage']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->order('id desc')
                    ->sum('Brokerage');
                $list=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->order('id desc')
                    ->paginate(15,false,['query'=>$get]);
                $page=$list->render();
                $list=$list->items();
                return view('transaction/showlist',['get'=>$get,'list'=>$list,'page'=>$page,'vip'=>$vipall,'agents'=>$agentsall,'acc'=>$acc,'count'=>$count]);
            }else{
                // 指定企业会员
                $agentsall=Db::name('users')->field('id,name')->where('register',2)->where('leaguer',$get['leaguer'])->select();
                foreach ($agentsall as $k=>$v){
                    if($v['id']==$get['agents']){
                        $agentsall[$k]['status']=1;
                    }else{
                        $agentsall[$k]['status']=0;
                    }
                }
                $count['Count']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->where('vip',$get['leaguer'])
                    ->order('id desc')
                    ->sum('Count');
                $count['Poundage']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->where('vip',$get['leaguer'])
                    ->order('id desc')
                    ->sum('Poundage');
                $count['Float']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->where('vip',$get['leaguer'])
                    ->order('id desc')
                    ->sum('`Float`');

                $count['Profit']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->where('vip',$get['leaguer'])
                    ->order('id desc')
                    ->sum('Profit');
                $count['Brokerage']=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->where('vip',$get['leaguer'])
                    ->order('id desc')
                    ->sum('Brokerage');


                $list=Db::name('transaction')
                    ->where('TradingDday','between',[$jystart,$jyend])
                    ->where('Contract','like','%'.$get['hy'].'%')
                    ->where('Subaccount','like','%'.$get['zzh'].'%')
                    ->where('Business','in',$mm)
                    ->where('Kaiping','in',$kp)
                    ->where('PrimaryAccount',$acctempop,$acctemp1)
                    ->where('vip',$get['leaguer'])
                    ->order('id desc')
                    ->paginate(15,false,['query'=>$get]);
                $page=$list->render();
                $list=$list->items();
                return view('transaction/showlist',['get'=>$get,'list'=>$list,'page'=>$page,'vip'=>$vipall,'agents'=>$agentsall,'acc'=>$acc,'count'=>$count]);
            }
        }else{
            // 制定代理商
            $agentsall=Db::name('users')->field('id,name')->where('register',2)->where('leaguer',$get['leaguer'])->select();
            foreach ($agentsall as $k=>$v){
                if($v['id']==$get['agents']){
                    $agentsall[$k]['status']=1;
                }else{
                    $agentsall[$k]['status']=0;
                }
            }
            $count['Count']=Db::name('transaction')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('Contract','like','%'.$get['hy'].'%')
                ->where('Subaccount','like','%'.$get['zzh'].'%')
                ->where('Business','in',$mm)
                ->where('Kaiping','in',$kp)
                ->where('PrimaryAccount',$acctempop,$acctemp1)
                ->where('agents',$get['agents'])
                ->order('id desc')
                ->sum('Count');
            $count['Poundage']=Db::name('transaction')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('Contract','like','%'.$get['hy'].'%')
                ->where('Subaccount','like','%'.$get['zzh'].'%')
                ->where('Business','in',$mm)
                ->where('Kaiping','in',$kp)
                ->where('PrimaryAccount',$acctempop,$acctemp1)
                ->where('agents',$get['agents'])
                ->order('id desc')
                ->sum('Poundage');
            $count['Float']=Db::name('transaction')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('Contract','like','%'.$get['hy'].'%')
                ->where('Subaccount','like','%'.$get['zzh'].'%')
                ->where('Business','in',$mm)
                ->where('Kaiping','in',$kp)
                ->where('PrimaryAccount',$acctempop,$acctemp1)
                ->where('agents',$get['agents'])
                ->order('id desc')
                ->sum('`Float`');


            $count['Profit']=Db::name('transaction')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('Contract','like','%'.$get['hy'].'%')
                ->where('Subaccount','like','%'.$get['zzh'].'%')
                ->where('Business','in',$mm)
                ->where('Kaiping','in',$kp)
                ->where('PrimaryAccount',$acctempop,$acctemp1)
                ->where('agents',$get['agents'])
                ->order('id desc')
                ->sum('Profit');
            $count['Brokerage']=Db::name('transaction')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('Contract','like','%'.$get['hy'].'%')
                ->where('Subaccount','like','%'.$get['zzh'].'%')
                ->where('Business','in',$mm)
                ->where('Kaiping','in',$kp)
                ->where('PrimaryAccount',$acctempop,$acctemp1)
                ->where('agents',$get['agents'])
                ->order('id desc')
                ->sum('Brokerage');


            $list=Db::name('transaction')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('Contract','like','%'.$get['hy'].'%')
                ->where('Subaccount','like','%'.$get['zzh'].'%')
                ->where('Business','in',$mm)
                ->where('Kaiping','in',$kp)
                ->where('PrimaryAccount',$acctempop,$acctemp1)
                ->where('agents',$get['agents'])
                ->order('id desc')
                ->paginate(15,false,['query'=>$get]);
            $page=$list->render();
            $list=$list->items();
            return view('transaction/showlist',['get'=>$get,'list'=>$list,'page'=>$page,'vip'=>$vipall,'agents'=>$agentsall,'acc'=>$acc,'count'=>$count]);
        }

    }

    public function xq(Request $request)
    {
        $id=$request->get('id');
        $list=Db::name('transaction')->find($id);
        return $list;
    }

    public function showlog(Request $request)
    {
        $get=$request->get();
        $get['type']=isset($get['type'])?$get['type']:'1,2,3,4,5,6,7,8';
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('-1 day'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d');
        $get['name']=isset($get['name'])?$get['name']:'';
        $get['tel']=isset($get['tel'])?$get['tel']:'';
//        $jystart=str_replace('-','',$get['start']);
//        $jyend=str_replace('-','',$get['end']);
        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
        $kp=explode(',',$get['type']);

//        $name='aa';
//        $temp=Db::name('users')->where('name','like','%'.$name.'%')->select();

        $data=Db::table('lc_cashlog')->alias(' a ')->join('lc_users b ' ,'b.id = a.uid')
            ->field('a.*,b.name,b.phonenumber')
            ->where('b.name','like','%'.$get['name'].'%')
            ->where('b.phonenumber','like','%'.$get['tel'].'%')
            ->where('a.type','in',$kp)
            ->where('a.time','between',[$get['start'],$jyend])
            ->order('a.id desc')
            ->paginate(15,false,['query'=>$get]);
        $zj=Db::table('lc_cashlog')->alias(' a ')->join('lc_users b ' ,'b.id = a.uid')
            ->field('a.*,b.name,b.phonenumber')
            ->where('b.name','like','%'.$get['name'].'%')
            ->where('b.phonenumber','like','%'.$get['tel'].'%')
            ->where('a.type','in',$kp)
            ->where('a.time','between',[$get['start'],$jyend])
            ->sum('price');
        $page=$data->render();
        $list=$data->items();
        foreach ($list as $k=>$v){
            $list[$k]['phonenumber']=substr_replace($v['phonenumber'], '****', 3, 4);
        }
        return view('transaction/showlog',['list'=>$list,'page'=>$page,'get'=>$get,'zj'=>$zj]);
    }

    public function showtel(Request $request)
    {
        $id=$request->get('id');
        $tel=Db::name('cashlog')->alias('a')->join('lc_users b','b.id=a.uid')
            ->field('b.phonenumber')
            ->where('a.id',$id)
            ->find()['phonenumber'];


//        $tel=Db::name('users')->where('id',$id)->find()['phonenumber'];
        $res=Db::name('showtel')->insert(['name'=>session('admin.name'),'tel'=>$tel]);
        if($tel){
            return ['status'=>1,'tel'=>$tel];
        }else{
            return ['status'=>2];
        }
    }

    // 佣金汇总 日期
    public function showsum(Request $request){
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        return view('transaction/showsum',['get'=>$get]);
    }
    // 佣金汇总 数据
    public function showsummary(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
        $viplist=Db::name('users')->field('id,name')->where('register',1)->paginate(15,false,['query'=>$get]);
        $page=$viplist->render();
        $list=$viplist->items();
        foreach ($list as $k=>$v){
//            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
//            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
//            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
//            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
            $list[$k]['tj']=Db::name('transaction')
                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('vip',$v['id'])
                ->find();
        }
        foreach ($list as $k=>$v){
            if($v['tj']['Poundage']==0){
                $list[$k]['tj']['fj']='';
                continue;
            }
            $list[$k]['tj']['fj']=$v['tj']['Poundage']-$v['tj']['Brokerage'];
        }
        $count['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Count');
        $count['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Poundage');
        $count['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Profit');
        $count['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Brokerage');
        $count['fj']=$count['Poundage']-$count['Brokerage'];
        return view('transaction/showsummary',['list'=>$list,'page'=>$page,'get'=>$get,'count'=>$count]);
    }
    // 佣金汇总 导出
    public function export(Request $request)
    {
        $get['start']=$request->get('start');
        $get['end']=$request->get('end');
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
//        var_dump($get);
        $list=Db::name('users')->field('id,name')->where('register',1)->select();
        foreach ($list as $k=>$v){
//            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
//            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
//            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
//            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
            $temp=Db::name('transaction')
                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('vip',$v['id'])
                ->find();
            if($temp['Poundage']==0){
                $list[$k]['fj']='';
                continue;
            }
            $list[$k]=array_merge($list[$k],$temp);
            $list[$k]['fj']=$list[$k]['Poundage']-$list[$k]['Brokerage'];
        }

        $count[0]['id']='总计';
        $count[0]['name']='';
        $count[0]['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Count');
        $count[0]['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Poundage');
        $count[0]['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Profit');
        $count[0]['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Brokerage');
        $count[0]['fj']=$count[0]['Poundage']-$count[0]['Brokerage'];

        $list=array_merge($list,$count);

//        $fileName .= "_" . date("Y_m_d", Request::instance()->time()) . ".xls";
        $fileName=$jystart.'-'.$jyend.'佣金汇总'.'.xls';
        $headArr=['企业id','企业名称','成交量','手续费','平仓盈亏','基础手续费','附加手续费'];
        $data=$list;
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties();

        $key = ord("A"); // 设置表头

        foreach ($headArr as $v) {

            $colum = chr($key);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $key += 1;

        }

        $column = 2;

        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }

        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();

    }

    // 充值统计日期页面
    public function czsum(Request $request){
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        return view('transaction/cz/showsum',['get'=>$get]);
    }

    // 充值统计数据
    public function czsummary(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
//        $jyend=str_replace('-','',$get['end']);
        $viplist=Db::name('users')->field('id,name')->where('register',1)->paginate(15,false,['query'=>$get]);
        $page=$viplist->render();
        $list=$viplist->items();
        foreach ($list as $k=>$v){
            $list[$k]['cz']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['cz']+=Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type','in','1,2,7')->sum('price');
            }
        }

//        var_dump($list);die;
//
//
//
//
//
//
//
//
//
//
//        foreach ($list as $k=>$v){
////            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
////            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
////            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
////            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
////            $list[$k]['tj']=Db::name('transaction')
////                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
////                ->where('TradingDday','between',[$jystart,$jyend])
////                ->where('vip',$v['id'])
////                ->find();
//        }
//        foreach ($list as $k=>$v){
//            if($v['tj']['Poundage']==0){
//                $list[$k]['tj']['fj']='';
//                continue;
//            }
//            $list[$k]['tj']['fj']=$v['tj']['Poundage']-$v['tj']['Brokerage'];
//        }
//        $count['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Count');
//        $count['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Poundage');
//        $count['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Profit');
//        $count['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Brokerage');
//        $count['fj']=$count['Poundage']-$count['Brokerage'];
        return view('transaction/cz/showsummary',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    // 充值统计导出
    public function czexport(Request $request)
    {

        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
//        $jyend=str_replace('-','',$get['end']);
        $list=Db::name('users')->field('id,name')->where('register',1)->select();
        foreach ($list as $k=>$v){
            $list[$k]['cz']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['cz']+=Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type','in','1,2,7')->sum('price');
            }
        }

//        $fileName .= "_" . date("Y_m_d", Request::instance()->time()) . ".xls";
        $fileName=$jystart.'-'.$jyend.'充值统计'.'.xls';
        $headArr=['企业id','企业名称','充值总额'];
        $data=$list;
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties();

        $key = ord("A"); // 设置表头

        foreach ($headArr as $v) {

            $colum = chr($key);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $key += 1;

        }

        $column = 2;

        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }

        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();

    }

    // 提现统计日期页面
    public function txsum(Request $request){
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        return view('transaction/tx/showsum',['get'=>$get]);
    }

    // 提现统计数据
    public function txsummary(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
//        $jyend=str_replace('-','',$get['end']);
        $viplist=Db::name('users')->field('id,name')->where('register',1)->paginate(15,false,['query'=>$get]);
        $page=$viplist->render();
        $list=$viplist->items();
        foreach ($list as $k=>$v){
            $list[$k]['tx']=0;
            $list[$k]['sxf']=0;
            $list[$k]['rg']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['tx']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',3)->sum('price'));
                $list[$k]['sxf']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',4)->sum('price'));
                $list[$k]['rg']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',8)->sum('price'));
            }
        }

//        var_dump($list);die;
//
//
//
//
//
//
//
//
//
//
//        foreach ($list as $k=>$v){
////            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
////            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
////            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
////            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
////            $list[$k]['tj']=Db::name('transaction')
////                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
////                ->where('TradingDday','between',[$jystart,$jyend])
////                ->where('vip',$v['id'])
////                ->find();
//        }
//        foreach ($list as $k=>$v){
//            if($v['tj']['Poundage']==0){
//                $list[$k]['tj']['fj']='';
//                continue;
//            }
//            $list[$k]['tj']['fj']=$v['tj']['Poundage']-$v['tj']['Brokerage'];
//        }
//        $count['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Count');
//        $count['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Poundage');
//        $count['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Profit');
//        $count['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Brokerage');
//        $count['fj']=$count['Poundage']-$count['Brokerage'];
        return view('transaction/tx/showsummary',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    // 提现统计导出
    public function txexport(Request $request)
    {

        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
//        $jyend=str_replace('-','',$get['end']);
        $list=Db::name('users')->field('id,name')->where('register',1)->select();
        foreach ($list as $k=>$v){
            $list[$k]['tx']=0;
            $list[$k]['sxf']=0;
            $list[$k]['rg']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['tx']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',3)->sum('price'));
                $list[$k]['sxf']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',4)->sum('price'));
                $list[$k]['rg']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',8)->sum('price'));
            }
        }

//        $fileName .= "_" . date("Y_m_d", Request::instance()->time()) . ".xls";
        $fileName=$jystart.'-'.$jyend.'提现统计'.'.xls';
        $headArr=['企业id','企业名称','提现总额','手续费总额','人工扣款总额'];
        $data=$list;
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties();

        $key = ord("A"); // 设置表头

        foreach ($headArr as $v) {

            $colum = chr($key);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $key += 1;

        }

        $column = 2;

        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }

        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();

    }

    // 入金统计日期页面
    public function rjsum(Request $request){
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        return view('transaction/rj/showsum',['get'=>$get]);
    }

    // 入金统计数据
    public function rjsummary(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
//        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
        $jyend=str_replace('-','',$get['end']);
        $jystart=date('Y-m-d H:i:s',strtotime($jystart)-3600*8);
        $jyend=date('Y-m-d H:i:s',strtotime($jyend)+3600*16);

        $viplist=Db::name('users')->field('id,name')->where('register',1)->paginate(15,false,['query'=>$get]);
        $page=$viplist->render();
        $list=$viplist->items();
        foreach ($list as $k=>$v){
            $list[$k]['rj']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['rj']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',5)->sum('price'));
            }
        }

//        var_dump($list);die;
//
//
//
//
//
//
//
//
//
//
//        foreach ($list as $k=>$v){
////            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
////            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
////            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
////            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
////            $list[$k]['tj']=Db::name('transaction')
////                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
////                ->where('TradingDday','between',[$jystart,$jyend])
////                ->where('vip',$v['id'])
////                ->find();
//        }
//        foreach ($list as $k=>$v){
//            if($v['tj']['Poundage']==0){
//                $list[$k]['tj']['fj']='';
//                continue;
//            }
//            $list[$k]['tj']['fj']=$v['tj']['Poundage']-$v['tj']['Brokerage'];
//        }
//        $count['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Count');
//        $count['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Poundage');
//        $count['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Profit');
//        $count['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Brokerage');
//        $count['fj']=$count['Poundage']-$count['Brokerage'];
        return view('transaction/rj/showsummary',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    // 入金统计导出
    public function rjexport(Request $request)
    {

        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
//        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
        $jyend=str_replace('-','',$get['end']);
        $name=$jystart.'-'.$jyend;
        $jystart=date('Y-m-d H:i:s',strtotime($jystart)-3600*8);
        $jyend=date('Y-m-d H:i:s',strtotime($jyend)+3600*16);

        $list=Db::name('users')->field('id,name')->where('register',1)->select();
        foreach ($list as $k=>$v){
            $list[$k]['rj']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['rj']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',5)->sum('price'));
            }
        }
//        $fileName .= "_" . date("Y_m_d", Request::instance()->time()) . ".xls";
        $fileName=$name.'入金统计'.'.xls';
        $headArr=['企业id','企业名称','入金总额'];
        $data=$list;
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties();

        $key = ord("A"); // 设置表头

        foreach ($headArr as $v) {

            $colum = chr($key);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $key += 1;

        }

        $column = 2;

        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }

        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();

    }

    // 出金统计日期页面
    public function cjsum(Request $request){
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        return view('transaction/cj/showsum',['get'=>$get]);
    }

    // 出金统计数据
    public function cjsummary(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
//        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
        $jyend=str_replace('-','',$get['end']);
        $jystart=date('Y-m-d H:i:s',strtotime($jystart)-3600*8);
        $jyend=date('Y-m-d H:i:s',strtotime($jyend)+3600*16);

        $viplist=Db::name('users')->field('id,name')->where('register',1)->paginate(15,false,['query'=>$get]);
        $page=$viplist->render();
        $list=$viplist->items();
        foreach ($list as $k=>$v){
            $list[$k]['cj']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['cj']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',6)->sum('price'));
            }
        }

//        var_dump($list);die;
//
//
//
//
//
//
//
//
//
//
//        foreach ($list as $k=>$v){
////            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
////            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
////            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
////            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
////            $list[$k]['tj']=Db::name('transaction')
////                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
////                ->where('TradingDday','between',[$jystart,$jyend])
////                ->where('vip',$v['id'])
////                ->find();
//        }
//        foreach ($list as $k=>$v){
//            if($v['tj']['Poundage']==0){
//                $list[$k]['tj']['fj']='';
//                continue;
//            }
//            $list[$k]['tj']['fj']=$v['tj']['Poundage']-$v['tj']['Brokerage'];
//        }
//        $count['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Count');
//        $count['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Poundage');
//        $count['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Profit');
//        $count['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->sum('Brokerage');
//        $count['fj']=$count['Poundage']-$count['Brokerage'];
        return view('transaction/cj/showsummary',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    // 出金统计导出
    public function cjexport(Request $request)
    {

        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $jystart=str_replace('-','',$get['start']);
//        $jyend=date('Y-m-d',strtotime($get['end'].'+1 day'));
        $jyend=str_replace('-','',$get['end']);
        $name=$jystart.'-'.$jyend;
        $jystart=date('Y-m-d H:i:s',strtotime($jystart)-3600*8);
        $jyend=date('Y-m-d H:i:s',strtotime($jyend)+3600*16);

        $list=Db::name('users')->field('id,name')->where('register',1)->select();
        foreach ($list as $k=>$v){
            $list[$k]['cj']=0;
            $temp=Db::name('users')->field('id')->where('register',3)->where('leaguer',$v['id'])->column('id');
            foreach ($temp as $key=>$value){
                $list[$k]['cj']+=abs(Db::name('cashlog')->where('uid',$value)->where('time','between',[$jystart,$jyend])->where('type',6)->sum('price'));
            }
        }
//        $fileName .= "_" . date("Y_m_d", Request::instance()->time()) . ".xls";





        $fileName=$name.'出金统计'.'.xls';
        $headArr=['企业id','企业名称','出金总额'];
        // $data是二维数组
        $data=$list;
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties();

        $key = ord("A"); // 设置表头

        foreach ($headArr as $v) {

            $colum = chr($key);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $key += 1;

        }

        $column = 2;

        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }

        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();

    }
    
    // 盈利总额汇总
    public function ylsum(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('-1 year'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d');
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
        $data=Db::name('transaction')->field('Subaccount,sum(Profit) as a,sum(`Float`) as b')->where('TradingDday','between',[$jystart,$jyend])->group('Subaccount')->order('a desc')->paginate(15,false,['query'=>$get]);
        $page=$data->render();
        $list=$data->items();
        return view('transaction/hz/ylsum',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    // 盈利次数汇总
    public function ylcssum(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('-1 year'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d');
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
        $data=Db::name('transaction')->field('Subaccount,sum(case when Profit>0 then 1 else 0 end) as a,sum(case when Profit<0 then 1 else 0 end) as c,sum(case when Kaiping = "平" then 1 when Kaiping = "平今" then 1 else 0 end) as d,sum(case when Profit>0 then 1 else 0 end)/sum(case when Kaiping = "平" then 1 when Kaiping = "平今" then 1 else 0 end) as e')->where('TradingDday','between',[$jystart,$jyend])->group('Subaccount')->order('d desc')->paginate(15,false,['query'=>$get]);
//        var_dump($data);die;
        $page=$data->render();
        $list=$data->items();
        return view('transaction/hz/ylcssum',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    // 交易量汇总
    public function countsum(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('-1 year'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d');
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
        $data=Db::name('transaction')->field('Subaccount,sum(case when Kaiping = "开" then Count else 0 end) as a,sum(case when Kaiping = "平" then Count else 0 end) as b,sum(case when Kaiping = "平今" then Count else 0 end) as c,sum(Count) as d')->where('TradingDday','between',[$jystart,$jyend])->group('Subaccount')->order('d desc')->paginate(15,false,['query'=>$get]);
        $page=$data->render();
        $list=$data->items();
        return view('transaction/hz/countsum',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    // 存在时间最长汇总
    public function timesum(Request $request)
    {
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('-1 year'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d');
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
        $data=Db::name('transaction')->field('Subaccount,min(TradingDday) as a,max(TradingDday) as b,datediff(max(TradingDday),min(TradingDday))+1 as c')->where('TradingDday','between',[$jystart,$jyend])->group('Subaccount')->order('c desc')->paginate(15,false,['query'=>$get]);
        $page=$data->render();
        $list=$data->items();
        return view('transaction/hz/timesum',['list'=>$list,'page'=>$page,'get'=>$get]);
    }

    public function aaaa(){

        
    }

    public function bb(){
        echo 111;
    }

}
